﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GBHNawaData.Dao
{
    public class GiDao
    {
        public decimal getQtyDelay(string custno, string matNo)
        {
            try
            {
                if (string.IsNullOrEmpty(custno) || string.IsNullOrEmpty(matNo))
                {
                    return 0;
                }

                using (var ctx = new GBHNawaData.GBHNawaDbEntities())
                {
                    string query = @"select so.mordno,so.morditemno,so.mactqty,bil.mbaseqty,bil.mbilltype
                                    from
                                    (select msoldto,mordno,morditemno,mactqty
                                    from vmsa551_saleorder_daily
                                    where msoldto = '{0}'
                                    and mstatus != 'D'
                                    and mmatno = '{1}'
                                    and msaledoctype in ('zz30','zz31','zz32','zz33','zz40','zz41','zz42','zz43')) so

                                    left join

                                    (select msoldto,msaledocno,msaleitemno,sum(mbaseqty) as mbaseqty,mbilltype
                                    from vmsa593_billdata_0280
                                    where msoldto = '{0}'
                                    and mmatno = '{1}'
                                    and mbilltype = 'ZZA1'
                                    group by msoldto,msaledocno,msaleitemno,mbilltype) bil

                                    on so.mordno = bil.msaledocno and so.morditemno = bil.msaleitemno and so.msoldto = bil.msoldto";

                    
                    System.Data.Entity.Infrastructure.DbRawSqlQuery<DelayOrderItem> orderGrp = ctx.Database.SqlQuery<DelayOrderItem>(string.Format(query, custno, matNo));

                    //var saleByOrderMatQR = (from sale in ctx.vmsa551_saleorder_daily
                    //                     join bill in ctx.vmsa593_billdata_0280
                    //                     on new { key1 = sale.mordno, key2 = sale.morditemno, key3 = sale.msoldto } equals
                    //                     new { key1 = bill.msaledocno, key2 = bill.msaleitemno, key3 = bill.msoldto }
                                       

                    //                     where sale.msoldto == custno && sale.mmatno == matNo && sale.mstatus != "D" && bill.mbilltype == "ZZA1"
                    //                     select new { sale.mactqty, bill.mbaseqty, sale.mordno, sale.morditemno});
                    //var saleByOrderMat = saleByOrderMatQR.ToList();
                    decimal sumOrderQty = 0;
                    decimal sumGiQty = 0;

                    //var orderGrp = from all in saleByOrderMatQR
                    //               group all by new { all.mordno, all.morditemno } into g
                    //               select new { ActQty = g.Max(all => all.mactqty),
                    //                            BastQty = g.Sum(all =>all.mbaseqty) } ;


                    if (orderGrp != null && orderGrp.Count() > 0)
                    {
                        sumOrderQty = orderGrp.Sum(o => o.mactqty ?? 0);
                        sumGiQty = orderGrp.Sum(o => o.mbaseqty?? 0);

                    }
                    return sumOrderQty - sumGiQty;
                }
            }
            catch (Exception ex)
            {

                return 0;
            }

        }
    }
}
